﻿Imports SERVICIOS

Public Class asistencia_data : Inherits Adaptador

    Event ErrorServer(ByVal Descripcion As String) 'evento de error que propaga el error de la clase adaptador (conexion a base de datos)
    Event GuardaCorrecto() 'evento propio de la clase, cuando guarda o realiza alguna operacion con exito.

    ''' <summary>
    ''' Devuelve todos los registros
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetData() As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT DISTINCT A.ASIS_FECHA, A.ASIS_DESCRIPCION FROM ASISTENCIA AS A"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    Public Function ObtenerNombresFuncionarios(ByVal fecha As Date, ByVal grupo_id As Integer) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT DISTINCT A.FUN_NOMBRE, F.FUN_GRADO, F.FUN_UNIDAD FROM ASISTENCIA AS A, FUNCIONARIO AS F " _
                & "WHERE F.FUN_NOMBRE = A.FUN_NOMBRE AND MONTH(A.ASIS_FECHA) = MONTH('" & fecha & "') AND F.GRUPO_ID = " & grupo_id
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try

    End Function

    Public Function ObtenerAsistenciasxNombresFuncionarios(ByVal nom As String) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT ESTADO, ASIS_ID FROM ASISTENCIA AS A WHERE A.FUN_NOMBRE = '" & nom & "'"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try

    End Function

    Public Function ObtenerCantColumnasxRegistro() As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT  DISTINCT COUNT(FUN_NOMBRE) as columnas FROM ASISTENCIA " _
                & "GROUP BY FUN_NOMBRE"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Buscar registros por COD/ID
    ''' </summary>
    ''' <param name="COD"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function msoBuscar(ByVal COD As Integer) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT A.*, F.FUN_GRADO, F.FUN_UNIDAD FROM ASISTENCIA as A, FUNCIONARIO as F " _
                & "WHERE F.FUN_NOMBRE = A.FUN_NOMBRE AND A.ASIS_ID = " & COD
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Guardar
    ''' </summary>
    ''' <param name="FECHA"></param>
    ''' <param name="FUN_NOMBRE"></param>
    ''' <param name="ESTADO"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function minGuarda(ByVal ID As Integer, _
                              ByVal FECHA As Date, _
                                ByVal FUN_NOMBRE As String, _
                                ByVal ESTADO As String, _
                                ByVal GRUPO As String)

        Try
            Dim SQL As String
            Dim dt As DataTable = msoBuscar(ID)
            Conectar()
            If dt.Rows.Count = 0 Then
                SQL = "INSERT INTO ASISTENCIA(ASIS_FECHA, FUN_NOMBRE, ESTADO, ASIS_DESCRIPCION) " _
                         & "values(" _
                         & "'" & FECHA.ToString("yyyy/MM/dd") & "'," _
                         & "'" & FUN_NOMBRE & "'," _
                          & "'" & ESTADO & "'," _
                         & "'" & GRUPO & "')"
            Else
                SQL = "UPDATE ASISTENCIA SET " _
                                    & " ASIS_FECHA = '" & FECHA.ToString("yyyy/MM/dd") & "', " _
                                    & " ESTADO =  '" & ESTADO & "', " _
                                    & " FUN_NOMBRE = '" & FUN_NOMBRE & "', " _
                                    & " ASIS_DESCRIPCION = '" & GRUPO & "' " _
                                    & " WHERE ASIS_ID = " & ID
            End If
            comando.CommandText = SQL
            comando.Connection = conn
            comando.ExecuteNonQuery()
            Desconectar()
            RaiseEvent GuardaCorrecto()
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function




    ''' <summary>
    ''' Devuelve error especifico de la conexion (clase adaptador)
    ''' </summary>
    ''' <param name="Descripcion"></param>
    ''' <remarks></remarks>
    Private Sub Adaptador_ErrorBaseServer(ByVal Descripcion As String) Handles Me.ErrorBaseServer
        RaiseEvent ErrorServer(Descripcion)
    End Sub

End Class
